const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'book_sharing',
  waitForConnections: true,
  connectionLimit: 10,        // 最大连接数限制
  queueLimit: 0,
  enableKeepAlive: true,      // 启用连接保活
  keepAliveInitialDelay: 0    // 立即开始保活检测
});

// 初始化数据库设置
const initDatabase = async () => {
  let connection;
  try {
    connection = await pool.getConnection();
    await connection.query('SET GLOBAL max_allowed_packet = 67108864');  // 64MB
    await connection.query('SET GLOBAL connect_timeout = 28800');  // 8小时
    await connection.query('SET GLOBAL wait_timeout = 28800');
    await connection.query('SET GLOBAL interactive_timeout = 28800');
    
    console.log('数据库初始化成功');
  } catch (err) {
    console.error('数据库初始化失败:', err);
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

// 执行 SQL 查询
const executeQuery = async (sql, params = []) => {
  let connection;
  try {
    connection = await pool.getConnection();
    const [results] = await connection.execute(sql, params);
    return results;
  } catch (error) {
    console.error('SQL执行错误:', {
      code: error.code,
      message: error.message,
      sql: error.sql,
      params
    });
    throw error;
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

// 初始化数据库
initDatabase();

// 监听连接池事件
pool.on('connection', () => console.log('新的数据库连接已创建'));
pool.on('release', () => console.log('数据库连接已释放'));
pool.on('error', err => console.error('连接池错误:', err));

module.exports = {
  executeQuery,
  pool
}; 